﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class ChuyenXeDAO
    {
        #region SelectALLMaChuyen
        public static List<String> SelectALLMaChuyen()
        {
            List<String> list = new List<String>();
            try
            {
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectALLChuyenXe");
                foreach (DataRow dr in dt.Rows)
                {

                    list.Add(dr["MACHUYEN"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion
        #region SelectMa
        public static List<ChuyenXeDTO> SelectMa()
        {
            List<ChuyenXeDTO> list = new List<ChuyenXeDTO>();
            try
            {
                DataTable dt = DataProvider.ExecuteQuery("usp_SelectMa");
                foreach (DataRow dr in dt.Rows)
                {
                    ChuyenXeDTO lx = new ChuyenXeDTO();
                    lx.MACHUYEN = dr["MACHUYEN"].ToString();
                    lx.BENXEDEN = dr["BENXEDEN"].ToString();
                    lx.BENXEDI = dr["BENXEDi"].ToString();
                    list.Add(lx);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion

        #region SelectALL
        public static List<ChuyenXeDTO> SelectALL()
        {
            List<ChuyenXeDTO> list = new List<ChuyenXeDTO>();
            try
            {
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectALLChuyenXe");
                foreach (DataRow dr in dt.Rows)
                {
                    ChuyenXeDTO cx = new ChuyenXeDTO();
                    cx.MACHUYEN = dr["MACHUYEN"].ToString();
                    cx.GIODI = DateTime.Parse(dr["GIODI"].ToString());
                    cx.GIODEN = DateTime.Parse(dr["GIODEN"].ToString());
                    cx.BENXEDEN = dr["BENXEDEN"].ToString();
                    cx.BENXEDI = dr["BENXEDI"].ToString();
                    list.Add(cx);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion


        #region SelectChuyenXeByMaChuyen
        public static ChuyenXeDTO SelectChuyenXeByMaChuyen(String machuyen)
        {
            ChuyenXeDTO cx = new ChuyenXeDTO();
            try
            {

                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MACHUYEN", machuyen));
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectChuyenXeByMaChuyen", sqlParams);
                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[0];
                    cx.MACHUYEN = dr["MACHUYEN"].ToString();
                    cx.GIODI = DateTime.Parse(dr["GIODI"].ToString());
                    cx.GIODEN = DateTime.Parse(dr["GIODEN"].ToString());
                    cx.BENXEDEN = dr["BENXEDEN"].ToString();
                    cx.BENXEDI = dr["BENXEDI"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return cx;
        }
        #endregion
        #region SelectChuyenXeByBenDen
        public static List<ChuyenXeDTO> SelectChuyenXeByBenDen(String benden)
        {
            List<ChuyenXeDTO> list = new List<ChuyenXeDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@BDen", benden));
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectChuyenXeByBXDen", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    ChuyenXeDTO cx = new ChuyenXeDTO();
                    cx.MACHUYEN = dr["MACHUYEN"].ToString();
                    cx.GIODI = DateTime.Parse(dr["GIODI"].ToString());
                    cx.GIODEN = DateTime.Parse(dr["GIODEN"].ToString());
                    cx.BENXEDEN = dr["BENXEDEN"].ToString();
                    cx.BENXEDI = dr["BENXEDI"].ToString();
                    list.Add(cx);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion

            public static bool themChuyenXe(String ma, String benDi, String benDen, String gioDi, String gioDen)
            {

                #region parameter
                SqlParameter pa0=new SqlParameter("@maChuyen",SqlDbType.VarChar);
                pa0.Value = ma;
                SqlParameter pa1 = new SqlParameter("@benDi", SqlDbType.VarChar);
                pa1.Value = benDi;
                SqlParameter pa2 = new SqlParameter("@benDen", SqlDbType.VarChar);
                pa2.Value = benDen;
                SqlParameter pa3=new SqlParameter("@gioDi",SqlDbType.VarChar);
                pa3.Value = gioDi;
                SqlParameter pa4 = new SqlParameter("@gioDen", SqlDbType.VarChar);
                pa4.Value = gioDen;
                #endregion
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                #region begin transaction
                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "aa");
                cmd.Transaction = tran;
                cmd.CommandText = "select * from ChuyenXe where MaChuyen=@maChuyen";
                cmd.Parameters.Add(pa0);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("Ma Chuyen Da Co");
                }
                dr.Close();
                cmd.CommandText = "select * from CHUYENXE where BENXEDEN=@benDi and BENXEDI=@benDen";
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)//= exists trog SQL
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("da ton tai chuyen xe nay");
                }
                dr.Close();
                cmd.Parameters.Clear();
                cmd.CommandText = "insert into CHUYENXE values(@maChuyen,@benDi,@benDen,@gioDi,@gioDen)";
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                cmd.Parameters.Add(pa4);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                tran.Commit();
                #endregion
                conn.Close();
                return true;
            }

            #region SuaChuyenXe
            public static void SuaChuyenXe(String ma, String benDi, String benDen, String gioDi, String gioDen)
            {

                #region parameter
                SqlParameter pa0 = new SqlParameter("@maChuyen", SqlDbType.VarChar);
                pa0.Value = ma;
                SqlParameter pa1 = new SqlParameter("@benDi", SqlDbType.VarChar);
                pa1.Value = benDi;
                SqlParameter pa2 = new SqlParameter("@benDen", SqlDbType.VarChar);
                pa2.Value = benDen;
                SqlParameter pa3 = new SqlParameter("@gioDi", SqlDbType.VarChar);
                pa3.Value = gioDi;
                SqlParameter pa4 = new SqlParameter("@gioDen", SqlDbType.VarChar);
                pa4.Value = gioDen;
                #endregion
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                #region begin transaction
                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "aa");
                cmd.Transaction = tran;
                cmd.CommandText = "select * from ChuyenXe where MaChuyen=@maChuyen";
                cmd.Parameters.Add(pa0);
                SqlDataReader dr = cmd.ExecuteReader();
                if (!dr.HasRows)
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("Ma Chuyen Da Co");
                }
                dr.Close();
                cmd.CommandText = "select * from CHUYENXE where BENXEDEN=@benDi and BENXEDI=@benDen";
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)//= exists trog SQL
                {
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Rollback();
                    throw new Exception("ko ton tai chuyen xe nay");
                }
                dr.Close();
                cmd.Parameters.Clear();
                cmd.CommandText = "update CHUYENXE set MACHUYEN=@machuyen,BENXEDI=@bendi,BENXEDEN=@benden,GIODI=@giodi,GIODEN=@gioden where MACHUYEN=@machuyen";
                
                cmd.Parameters.Add(pa0);
                cmd.Parameters.Add(pa1);
                cmd.Parameters.Add(pa2);
                cmd.Parameters.Add(pa3);
                cmd.Parameters.Add(pa4);
                cmd.ExecuteNonQuery();

                //cmd.Parameters.Clear();
                //cmd.CommandText = "insert into CHUYENXE values(@maChuyen,@benDi,@benDen,@gioDi,@gioDen)";
                //cmd.Parameters.Add(pa0);
                //cmd.Parameters.Add(pa1);
                //cmd.Parameters.Add(pa2);
                //cmd.Parameters.Add(pa3);
                //cmd.Parameters.Add(pa4);
                //cmd.ExecuteNonQuery();

                cmd.Dispose();
                tran.Commit();
                #endregion
                conn.Close();
                
            }
            #endregion
            public static void XoaChuyenXe(String ma)
            {

                #region parameter
                SqlParameter pa0 = new SqlParameter("@maChuyen", SqlDbType.VarChar);
                pa0.Value = ma;
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;

                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "aa");
                cmd.Transaction = tran;
                cmd.CommandText = "Delete CHUYENXE where MACHUYEN=@machuyen";

                cmd.Parameters.Add(pa0);
                
                cmd.ExecuteNonQuery();

                //cmd.Parameters.Clear();
                //cmd.CommandText = "insert into CHUYENXE values(@maChuyen,@benDi,@benDen,@gioDi,@gioDen)";
                //cmd.Parameters.Add(pa0);
                //cmd.Parameters.Add(pa1);
                //cmd.Parameters.Add(pa2);
                //cmd.Parameters.Add(pa3);
                //cmd.Parameters.Add(pa4);
                //cmd.ExecuteNonQuery();

                cmd.Dispose();
                tran.Commit();
                #endregion
                conn.Close();
            }

           
        
        
    }
}
